package com.contexttree.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;

import com.contexttree.utils.Printer;

/**
 * This class manage all operations with MySql database such as connection ad execute query.
 * Politecnico di Milano 2012
 * @author Paolo Barbaglia
 * @author Filippo Ferrari
 *
 */
public class DatabaseManager {
	
	private String dbName;
	private String userName;
	private String userPass;
	private String error;
	private Connection db;
	private boolean connected;
	
	public DatabaseManager(String dbName){ 
		this(dbName, "", "");
	}

	public DatabaseManager(String dbName, String userName, String userPass) {
		this.dbName = dbName;
		this.userName = userName;
		this.userPass = userPass;
		connected = false;
		error = "";
	}
	
	/**
    * Open a connection with database
    * @return connected
    */
	public boolean connect(){
      connected = false;
      try {
    	  //Load driver JDBC for MySQL database
    	  Class.forName("com.mysql.jdbc.Driver");
    	  
    	  if (!dbName.equals("")){
    		  if (userName.equals("")){
    			  //Connection with only db name
    			  db = DriverManager.getConnection("jdbc:mysql://localhost/" + dbName);
			  }else{
				  //Connection with user and pass
				  if (userPass.equals("")){
					  //Connection without pass
					  db = DriverManager.getConnection("jdbc:mysql://localhost/" + dbName + "?user=" + userName);
				  }else{
					  //Connection with pass
					  db = DriverManager.getConnection("jdbc:mysql://localhost/" + dbName + "?user=" + userName + "&password=" + userPass);
				  }
			  }
    		  //Connection OK
    		  connected = true;
		  } else {
			  Printer.print("Manca il nome del database!!");
			  Printer.print("Scrivere il nome del database da utilizzare all'interno del file \"config.xml\"");
			  System.exit(0);
         }
      } catch (Exception e){ 
    	  error = e.getMessage();
    	  }
      return connected;
   }
	
	/**
    * Execute a sql selection query on data of database
    * query: A string that represent an SQL selection query
    * columns: the number of columns of which the result row are made
    * Return a vector with all records of result
    * @param query
    * @return v
    */
	public ArrayList<String> executeQuery(String query){
		ArrayList<String> stringList = new ArrayList<String>();
		int columns = 2;
		try{
			//Statement for query execution
			Statement stmt = db.createStatement();
			//Resultset of execution
			ResultSet rs = stmt.executeQuery(query);
			ResultSetMetaData rsmd = rs.getMetaData();
			columns = rsmd.getColumnCount();
			
			//Write metadata query on ArrayList
			String strHeader = "";
			for(int i = 1; i < columns+1; i++) {
				if(i<columns){
					strHeader = strHeader + rsmd.getColumnName(i) + ",";
				} else {
					strHeader = strHeader + rsmd.getColumnName(i);
				}
			}
			stringList.add(strHeader);
			
			while(rs.next()){
				//Create arraylist with resultset
				String str = "";
				for (int i = 1; i < columns+1; i++){
					if(i<columns) {
						str = str + rs.getString(i) + ",";
					} else {
						str = str + rs.getString(i);
					}
				}
				stringList.add(str.replaceAll("\\n|\\r", ""));
			}
		//Close resultset
		rs.close();
		//Close statement
		stmt.close();
		} catch (Exception e){
			e.printStackTrace(); 
			error = e.getMessage();
		}
	return stringList;
	}
	   
   /**
    * Close the database connection
    */
   public void disconnect() {
      try {
         db.close();
         connected = false;
      } catch (Exception e) { 
    	  e.printStackTrace();
	  }
   }
   
   /**
    * Return true if the database connection is active
    * @return connected
    */
   public boolean isConnected() { 
	   return connected;
   }   
   
   /**
    * Return message error from last exception
    * @return error
    */
   public String getError() { 
	   return error;
   }
}